package com.market.dao.jpa;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.market.dao.ProductPriceDao;
import com.market.dao.WeeklyDao;
import com.market.domain.Product;
import com.market.domain.ProductPrice;
import com.market.domain.Role;
import com.market.domain.Weekly;

@Repository("weeklyDao")
public class WeeklyDaoJpa extends GenericDaoJpa<Weekly, Long> implements
		WeeklyDao {

	@Autowired
	private ProductPriceDao priceDao;

	public WeeklyDaoJpa() {
		super(Weekly.class);
	}

	@Override
	public List<Weekly> findAll() {
		String sql = "select weekly.* from weekly order by year desc, weekOfYear desc";
		Query q = this.em.createNativeQuery(sql, Weekly.class);
		List<Weekly> weeklys = q.getResultList();
		return weeklys;
	}

	public void remove(Weekly weekly) {
		if (this.findById(weekly.getId()) == null) {
			log.debug(this.getClass() + " Weekly with id " + weekly.getId()
					+ " is not found, can not be deleted");
		} else {
			log.debug(this.getClass() + " Weekly with id " + weekly.getId()
					+ " is found, will be deleted");
			super.removeById(weekly.getId());
		}

	}

	public List<Product> findProductsInStockNotInWeekly(Weekly weekly) {
		String sql = "select product.* from product where product.inventory>0 and  product.id not in "
				+ "( select productprice.product_id from productprice,weekly_productprice where productprice.id =  "
				+ " weekly_productprice.productprices_id and weekly_productprice.weekly_id= '"
				+ weekly.getId() + "')";
		Query query = this.em.createNativeQuery(sql, Product.class);
		List<Product> products =  query.getResultList();
		return products;
	}

	public List<ProductPrice> findProductPrices(Weekly weekly) {
		String sql = "select productprice.* from productprice, weekly_productprice ,product where "
				+ " weekly_productprice.productprices_id=productprice.id and productprice.product_id=product.id and weekly_productprice.weekly_id = '"
				+ weekly.getId() + "' order by product.recommendIndex desc, product.name asc";
		Query query = this.em.createNativeQuery(sql, ProductPrice.class);
		List<ProductPrice> productPrices = query.getResultList();
		return productPrices;
	}

	public List<Weekly> findActiveWeeklys() {
		String sql = "select * from weekly where isactive=1 order by year desc, weekOfYear desc";
		Query query = this.em.createNativeQuery(sql, Weekly.class);
		List<Weekly> weeklys = query.getResultList();
		return weeklys;
	}

	@Override
	public Weekly save(Weekly weekly) {
		// Delete from productprice first, because save weekly will add records
		// to productprice.
		// Weekly's productPrices property Cascade have set to all.
		String sql = "delete from productprice where productprice.id in "
				+ "(select productprices_id from weekly_productprice where weekly_productprice.weekly_id= "
				+ weekly.getId() + ")";
		Query query = this.em.createNativeQuery(sql);
		query.executeUpdate();

		Weekly week = super.save(weekly);

		return week;
	}

}
